package com.easy.framework.poi.utils;

import com.easy.framework.cache.utils.DictUtil;
import com.easy.framework.common.utils.ConvertUtil;
import com.easy.framework.common.utils.Func;
import com.easy.framework.common.utils.date.DateTimeUtil;
import com.easy.framework.common.utils.date.DateUtils;
import com.easy.framework.poi.annotation.Excel;
import com.easy.framework.poi.annotation.Option;
import lombok.extern.log4j.Log4j2;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.ReflectionUtils;

import jakarta.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

@Log4j2
public class ExcelUtils {

    /**
     * 导出Excel
     */
    public static <T> void exportExcel(List<T> list, OutputStream outputStream, String sheetName) throws IOException {
        if (list == null || list.isEmpty()) {
            throw new IllegalArgumentException("导出数据不能为空");
        }

        // 获取注解配置
        Class<?> clazz = list.get(0).getClass();
        List<Field> fields = getExcelFields(clazz);
        System.out.println(XSSFWorkbook.class.getClassLoader());
        // 创建工作簿
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet(Func.isEmpty(sheetName) ? "Sheet1" : sheetName);

        // 创建标题行
        Row titleRow = sheet.createRow(0);
        for (int i = 0; i < fields.size(); i++) {
            Field field = fields.get(i);
            Excel excel = field.getAnnotation(Excel.class);
            Cell cell = titleRow.createCell(i);
            cell.setCellValue(excel.name());
            // 设置列宽
            sheet.setColumnWidth(i, excel.export().width() * 256);
        }

        // 填充数据
        for (int i = 0; i < list.size(); i++) {
            T item = list.get(i);
            Row row = sheet.createRow(i + 1);

            for (int j = 0; j < fields.size(); j++) {
                Field field = fields.get(j);
                ReflectionUtils.makeAccessible(field);
                Excel excel = field.getAnnotation(Excel.class);
                if (!excel.export().enabled()) {
                    continue;
                }
                Object value = ReflectionUtils.getField(field, item);
                Cell cell = row.createCell(j);
                // 处理值转译
                value = convertValue(value, excel);

                // 设置单元格值
                setCellValue(cell, value);

                // 设置单元格样式
                CellStyle cellStyle = workbook.createCellStyle();
                setCellStyle(cellStyle, excel);
                cell.setCellStyle(cellStyle);
            }
        }

        workbook.write(outputStream);
        workbook.close();
    }

    public static <T> void exportExcel(HttpServletResponse response, List<T> list, String filename, String sheetName) throws IOException {

        if (Func.isEmpty(filename)) {
            filename = (list.hashCode() < 0 ? Math.abs(list.hashCode()) : list.hashCode()) + "";
        }
        // 设置响应头
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment;filename=" + filename + ".xlsx");

        // 导出Excel
        OutputStream outputStream = response.getOutputStream();
        ExcelUtils.exportExcel(list, outputStream, sheetName);
        outputStream.flush();
        outputStream.close();
    }

    public static <T> void exportExcel(HttpServletResponse response, List<T> list, String filename) throws IOException {
        exportExcel(response, list, filename, null);
    }

    /**
     * 导入Excel
     */
    public static <T> List<T> importExcel(InputStream inputStream, Class<T> clazz) throws InstantiationException, IllegalAccessException, IOException {
        Workbook workbook = WorkbookFactory.create(inputStream);
        Sheet sheet = workbook.getSheetAt(0);

        // 获取注解配置
        List<Field> fields = getExcelFields(clazz);
        Map<String, Field> fieldMap = new HashMap<>();
        for (Field field : fields) {
            Excel excel = field.getAnnotation(Excel.class);
            ReflectionUtils.makeAccessible(field);
            fieldMap.put(excel.name(), field);
        }

        // 解析标题行
        Row titleRow = sheet.getRow(0);
        Map<Integer, Field> columnMap = new HashMap<>();
        for (int i = 0; i < titleRow.getLastCellNum(); i++) {
            Cell cell = titleRow.getCell(i);
            if (cell != null) {
                String title = cell.getStringCellValue();
                Field field = fieldMap.get(title);
                if (field != null && field.getAnnotation(Excel.class).importable().enabled()) {
                    columnMap.put(i, field);
                }
            }
        }

        // 解析数据行
        List<T> result = new ArrayList<>();
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                continue;
            }

            T obj = clazz.newInstance();
            boolean hasValue = false;

            for (int j = 0; j < row.getLastCellNum(); j++) {
                Cell cell = row.getCell(j);
                if (cell == null || !columnMap.containsKey(j)) {
                    continue;
                }

                Field field = columnMap.get(j);
                Excel excel = field.getAnnotation(Excel.class);

                Object value = getCellValue(cell, field.getType());

                // 值转译
                value = reverseConvertValue(value, excel);

                // 校验必填
                if (excel.importable().required() && value == null) {
                    throw new RuntimeException("第" + (i + 1) + "行" + excel.name() + "不能为空");
                }

                // 正则校验
                if (value != null && !excel.importable().regex().isEmpty() && !value.toString().matches(excel.importable().regex())) {
                    throw new RuntimeException("第" + (i + 1) + "行" + excel.name() + "格式错误: " + excel.importable().regexMessage());
                }

                ReflectionUtils.setField(field, obj, value);
                hasValue = true;
            }

            if (hasValue) {
                result.add(obj);
            }
        }

        workbook.close();
        return result;
    }

    // 辅助方法
    private static List<Field> getExcelFields(Class<?> clazz) {
        List<Field> fields = new ArrayList<>();
        ReflectionUtils.doWithFields(clazz, field -> {
            if (field.isAnnotationPresent(Excel.class)) {
                fields.add(field);
            }
        });

        // 按sort排序
        fields.sort(Comparator.comparingInt(f -> f.getAnnotation(Excel.class).order()));
        return fields;
    }

    private static Object convertValue(Object value, Excel excel) {
        if (value == null) {
            return null;
        }

        //转译
        if (Func.isNotEmpty(excel.options())) {
            for (Option option : excel.options()) {
                if (option.value().equals(value.toString())) {
                    value = option.name();
                }
            }
        } else if (!excel.dictType().isEmpty()) {
            // 这里可以从缓存或数据库获取字典值
            value = DictUtil.getDictLabel(excel.dictType(), value.toString());
        }


        // 日期格式化
        if (value instanceof Date) {
            if (!excel.export().format().isEmpty()) {
                return DateUtils.format((Date) value, excel.export().format());
            }
            return DateUtils.format((Date) value, DateUtils.PATTERN_DATETIME);
        }

        // 数字格式化
        if (value instanceof Number && !excel.export().format().isEmpty()) {
            // 使用DecimalFormat格式化数字
            DecimalFormat df = new DecimalFormat(excel.export().format());
            return df.format(value);
        }

        return value;
    }

    private static Object reverseConvertValue(Object value, Excel excel) {
        if (value == null) {
            return null;
        }
        // 字典反向转译
        if (!excel.dictType().isEmpty()) {
            // 这里可以从缓存或数据库获取字典值
            value = DictUtil.getDictValue(excel.dictType(), value.toString());
        }
        return value;
    }

    private static void setCellValue(Cell cell, Object value) {
        if (value == null) {
            cell.setCellValue("");
        } else if (value instanceof String) {
            cell.setCellValue((String) value);
        } else if (value instanceof Number) {
            cell.setCellValue(((Number) value).doubleValue());
        } else if (value instanceof Boolean) {
            cell.setCellValue((Boolean) value);
        } else if (value instanceof Date) {
            cell.setCellValue((Date) value);
        } else {
            cell.setCellValue(value.toString());
        }
    }

    private static Object getCellValue(Cell cell, Class<?> fieldType) {
        if (cell == null) {
            return null;
        }

        switch (cell.getCellType()) {
            case STRING:
                String strValue = cell.getStringCellValue().trim();
                if (fieldType == String.class) {
                    return strValue;
                } else if (fieldType == Integer.class || fieldType == int.class) {
                    return Integer.parseInt(strValue);
                } else if (fieldType == Long.class || fieldType == long.class) {
                    return Long.parseLong(strValue);
                } else if (fieldType == Double.class || fieldType == double.class) {
                    return Double.parseDouble(strValue);
                } else if (fieldType == Boolean.class || fieldType == boolean.class) {
                    return ConvertUtil.toBoolean(strValue);
                } else {
                    return strValue;
                }
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue();
                } else {
                    double numValue = cell.getNumericCellValue();
                    if (fieldType == Integer.class || fieldType == int.class) {
                        return (int) numValue;
                    } else if (fieldType == Long.class || fieldType == long.class) {
                        return (long) numValue;
                    } else if (fieldType == Double.class || fieldType == double.class) {
                        return numValue;
                    } else {
                        return numValue;
                    }
                }
            case BOOLEAN:
                return cell.getBooleanCellValue();
            case FORMULA:
                return cell.getCellFormula();
            case BLANK:
                return null;
            default:
                return null;
        }
    }

    private static void setCellStyle(CellStyle cellStyle, Excel excel) {
        // 设置对齐方式
        switch (excel.export().align()) {
            case ATUO:
                cellStyle.setAlignment(HorizontalAlignment.LEFT);
                break;
            case CENTER:
                cellStyle.setAlignment(HorizontalAlignment.CENTER);
                break;
            case RIGHT:
                cellStyle.setAlignment(HorizontalAlignment.RIGHT);
                break;
            default:
                cellStyle.setAlignment(HorizontalAlignment.GENERAL);
        }
    }
}